﻿using MongoDB.Driver;
using Sustainalytics.MongoDB.Extensions;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SpreadsheetGear;
using Sustainalytics.ComprehensiveESGResearch.Service.Model;
using Sustainalytics.BusinessResearchProductStates.Entities.ResearchState;
using System;

namespace Sustainalytics.ComprehensiveESGResearch.Service.Features
{
    public class ComprehensiveESGResearchHandler
    {
        private readonly IMongoCollection<ConnectionStrings> connectionStringsCollection;
        private readonly ConnectionStrings connectionStrings;

        public ComprehensiveESGResearchHandler(IMongoCollection<ConnectionStrings> connectionStringsCollection)
        {
            this.connectionStringsCollection = connectionStringsCollection;
            this.connectionStrings = connectionStringsCollection.Find(x => true).Limit(1).First();
        }

        public async Task<IWorkbook> GenerateReport()
        {
            IWorkbook workbook = Factory.GetWorkbook();
            var corporateData = await GetCorporateData().ConfigureAwait(false);

            ConstructCompanyLevelWorkSheet(workbook, corporateData);
            ConstructCompanyKeyESGIssuesWorksheet(workbook, corporateData);
            ConstructKeyESGIssueIndicatorLevelWorksheet(workbook, corporateData);
            ConstructAllKeyESGIssuesWorksheet(workbook, corporateData);
            ConstructIndicatorLevelWorksheet(workbook, corporateData);
            ConstructSourcesWorkSheet(workbook, corporateData);
            ConstructIndustryLeadWorksheet(workbook, corporateData);

            return workbook;
        }

        public async Task<IEnumerable<CorporateDataDto>> GetCorporateData()
        {
            try
            {
                var corporateDataCollection = MongoFactory.GetCollectionFromDatabase<CorporateDataDto>(connectionStrings.ClientDW, "CorporateData");
                var rpCompanyESGComputationResultCollection = MongoFactory.GetCollectionFromDatabase<RPCompanyEsgComputationResultDto>(connectionStrings.ClientDW, "RPCompanyESGComputationResult");
                var companyKeyESGIssueCollection = MongoFactory.GetCollectionFromDatabase<CompanyKeyEsgIssueDto>(connectionStrings.ClientDW, "CompanyKeyESGIssue");
                var rpIndicatorExtendedCollection = MongoFactory.GetCollectionFromDatabase<RPIndicatorExtendedDto>(connectionStrings.ClientDW, "RPIndicatorExtended");
                var sourceCollection = MongoFactory.GetCollectionFromDatabase<SourceDto>(connectionStrings.ClientDW, "Source");


                var corporateData = (await corporateDataCollection.Aggregate().Match(x => x.ComprehensiveEsgResearchState.ResearchState == ResearchState.ResearchAvailable)
                                        .Lookup<CorporateDataDto, RPCompanyEsgComputationResultDto, CorporateDataDto>(rpCompanyESGComputationResultCollection, cd => cd.Id, rpcece => rpcece.Id, x => x.RPCompanyESGComputationResults)
                                        .Lookup<CorporateDataDto, CompanyKeyEsgIssueDto, CorporateDataDto>(companyKeyESGIssueCollection, cd => cd.Id, rpcece => rpcece.Id, x => x.CompanyKeyESGIssues)
                                        .ToListAsync()).ToDictionary(x => x.Id, y => y);

                var corporateDataIds = corporateData.Keys;

                var sourceList = sourceCollection.Find(x => corporateDataIds.Contains(x.CompanyId)).ToList();
                sourceList.ForEach(x =>
                {
                    corporateData[x.CompanyId].Sources = corporateData[x.CompanyId].Sources ?? new List<SourceDto>();
                    corporateData[x.CompanyId].Sources.Add(x);
                });

                var rpIndicatorExtendedList = rpIndicatorExtendedCollection.Find(x => corporateDataIds.Contains(x.CompanyId) && x.Weight != null).ToList();
                rpIndicatorExtendedList.ForEach(x =>
                {

                    corporateData[x.CompanyId].RPIndicatorExtendedResults = corporateData[x.CompanyId].RPIndicatorExtendedResults
                                                                            ?? new List<RPIndicatorExtendedDto>();
                    corporateData[x.CompanyId].RPIndicatorExtendedResults.Add(x);

                });

                return corporateData.Values;
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
        }

        public void ConstructSourcesWorkSheet(IWorkbook workbook, IEnumerable<CorporateDataDto> corporateData)
        {
            IWorksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "Sources";

            var sources = GetSourcesData(corporateData);
            AddSourcesHeaders(worksheet);
            AddSourcesRows(worksheet, sources);

        }

        public void ConstructCompanyLevelWorkSheet(IWorkbook workbook, IEnumerable<CorporateDataDto> corporateData)
        {

            IWorksheet worksheet = workbook.Worksheets["Sheet1"];
            worksheet.Name = "Company_level";
            var companyLevelData = GetCompanyLevelData(corporateData);
            AddCompanyLevelHeaders(worksheet);
            AddCompanyLevelRows(worksheet, companyLevelData);
        }

        private void ConstructCompanyKeyESGIssuesWorksheet(IWorkbook workbook, IEnumerable<CorporateDataDto> corporateData)
        {
            IWorksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "Company_Key_ESG_Issues";

            var keyESGIssues = GetCompanyKeyESGIssuesData(corporateData);
            AddCompanyKeyESGIssuesHeaders(worksheet);
            AddCompanyKeyESGIssuesRows(worksheet, keyESGIssues);
        }

        private void ConstructKeyESGIssueIndicatorLevelWorksheet(IWorkbook workbook, IEnumerable<CorporateDataDto> corporateData)
        {
            IWorksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "Key_ESG_Issue_Indicator_Level";

            var keyESGIssueIndicators = GetKeyESGIssuesIndicatorData(corporateData);
            AddKeyESGIssueIndicatorsHeaders(worksheet);
            AddKeyESGIssueIndicatorsRows(worksheet, keyESGIssueIndicators);

        }

        private void ConstructAllKeyESGIssuesWorksheet(IWorkbook workbook, IEnumerable<CorporateDataDto> corporateData)
        {
            IWorksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "All_Key_ESG_Issues";

            var allKeyESGIssues = GetAllKeyESGIssuesData(corporateData);
            AddAllKeyEsgIssuesHeaders(worksheet);
            AddAllKeyEsgIssuesRows(worksheet, allKeyESGIssues);

        }

        private void ConstructIndicatorLevelWorksheet(IWorkbook workbook, IEnumerable<CorporateDataDto> corporateData)
        {
            IWorksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "Indicator_Level";

            var indicatorLevels = GetIndicatorLevelData(corporateData);
            AddIndicatorLevelHeaders(worksheet);
            AddIndicatorLevelRows(worksheet, indicatorLevels);
        }

        private void ConstructIndustryLeadWorksheet(IWorkbook workbook, IEnumerable<CorporateDataDto> corporateData)
        {
            IWorksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "Industry_Lead";

            var industryLeads = GetIndustryLeadData(corporateData);
            AddIndustryLeadHeaders(worksheet);
            AddIndustryLeadRows(worksheet, industryLeads);
        }

        private IEnumerable<IndustryLeadSheetDto> GetIndustryLeadData(IEnumerable<CorporateDataDto> corporateData)
        {
            var analystCollection = MongoFactory.GetCollectionFromDatabase<AnalystDto>(connectionStrings.ClientDW, "Analyst");
            var rpCompanyProfileCollection = MongoFactory.GetCollectionFromDatabase<RPCompanyProfileDto>(connectionStrings.ClientDW, "RPCompanyProfile");

            var companyCapitalIqIds = corporateData.Select(cd => cd.CapitalIqId);

            var rpCompanyProfileList = rpCompanyProfileCollection.Find(x => companyCapitalIqIds.Contains(x.CapitalIqId))
                                        .ToList();
            var analystDic = analystCollection.Find(x => true).ToList().ToDictionary(x => x.AnalystId, y => $"{y.FirstName} {y.LastName}");
            var data = corporateData.Join(rpCompanyProfileList, cd => cd.CapitalIqId, rpcp => rpcp.CapitalIqId, (cd, rpcp) =>
            {
                return new IndustryLeadSheetDto
                {
                    CapitalIqId = cd.CapitalIqId,
                    PeerGroup = cd.PeerGroup,
                    IndustryLead = analystDic.ContainsKey(rpcp.AnalystId) ? analystDic[rpcp.AnalystId] : string.Empty
                };
            });


            return data;
        }

        private IEnumerable<SourceSheetDto> GetSourcesData(IEnumerable<CorporateDataDto> corporateData)
        {
            var indicatorTemplateCollection = MongoFactory.GetCollectionFromDatabase<IndicatorTemplateDto>(connectionStrings.ClientDW, "IndicatorTemplate");
            var indicatorTemplateDic = indicatorTemplateCollection.Find(x => true).ToList().ToDictionary(x => x.Number, y => y.Name);


            var data = corporateData.SelectMany(cd =>
                (cd.Sources ?? new List<SourceDto>()).SelectMany(s => (s.IndicatorsNumbers ?? new List<string>()).Select(i => new
                {
                    CapitalIqId = cd.CapitalIqId,
                    SourceText = s.SourceText,
                    IndicatorNumber = i
                }))).GroupBy(x => new { x.CapitalIqId, x.IndicatorNumber }).Select(x => new SourceSheetDto
                {
                    CapitalIqId = x.Key.CapitalIqId,
                    IndicatorCode = x.Key.IndicatorNumber,
                    IndicatorName = indicatorTemplateDic[x.Key.IndicatorNumber],
                    Sources = x.Select(st => st.SourceText).Aggregate((a, b) => a + " | " + b)
                });

            return data;
        }

        private IEnumerable<KeyEsgIssueSheetDto> GetAllKeyESGIssuesData(IEnumerable<CorporateDataDto> corporateData)
        {
            var data = corporateData.SelectMany(cd =>
                (cd.CompanyKeyESGIssues.FirstOrDefault()?.KeyESGIssues ?? new List<KeyEsgIssueDto>())
                    .Select(x => new KeyEsgIssueSheetDto
                    {
                        ExternalId = x.ExternalId,
                        Name = x.Name,
                        Description = x.Description
                    })
                );

            return data;
        }

        private IEnumerable<CompanyLevelSheetDto> GetCompanyLevelData(IEnumerable<CorporateDataDto> corporateData)
        {

            var esgFocusThresholdsCollectionDic = new Dictionary<string, Dictionary<string, string>>();
            var esgFocusThresholdsCollection = MongoFactory.GetCollectionFromDatabase<EsgFocusThresholdsDto>(connectionStrings.ClientDW, "EsgFocusThresholds").AsQueryable();

            var rpCompanyProfileCollection = MongoFactory.GetCollectionFromDatabase<RPCompanyProfileDto>(connectionStrings.ClientDW, "RPCompanyProfile");


            var companyCapitalIqIds = corporateData.Select(cd => cd.CapitalIqId);

            var rpCompanyProfileDic = rpCompanyProfileCollection.Find(x => companyCapitalIqIds.Contains(x.CapitalIqId))
                                        .ToList().ToDictionary(x => x.CapitalIqId, y => y);

            esgFocusThresholdsCollection.ToList().ForEach(x =>
            {
                if (!esgFocusThresholdsCollectionDic.ContainsKey(x.PeerGroup))
                {
                    esgFocusThresholdsCollectionDic.Add(x.PeerGroup, new Dictionary<string, string>() { { x.Value, $"{x.LowerBound}, {x.UpperBound}" } });
                }
                else if (esgFocusThresholdsCollectionDic.ContainsKey(x.PeerGroup) && !esgFocusThresholdsCollectionDic[x.PeerGroup].ContainsKey(x.Value))
                {
                    esgFocusThresholdsCollectionDic[x.PeerGroup].Add(x.Value, $"{x.LowerBound}, {x.UpperBound}");
                }

            });

            var companyLevelData = corporateData.Select(cd =>
            {
                var rpcCompanyESGComputationResult = cd.RPCompanyESGComputationResults.FirstOrDefault();
                return new CompanyLevelSheetDto
                {
                    CapitalIqId = cd.CapitalIqId,
                    AnalystCommentary = rpCompanyProfileDic.ContainsKey(cd.CapitalIqId) ? rpCompanyProfileDic[cd.CapitalIqId].AnalystCommentary : null,
                    EscTotalScoreOverallScore = rpcCompanyESGComputationResult?.OverallPerformance?.Score,
                    CompanyAssessment = rpcCompanyESGComputationResult?.OverallPerformance?.Assessment.Name,
                    PeerGroup = cd.PeerGroup,
                    PeerGroupCode = cd.SustainalyticsSubIndustryCode,
                    EnvironmentThemeScore = rpcCompanyESGComputationResult?.EnvironmentPerformance?.Score,
                    EnvironmentThemeWeight = rpcCompanyESGComputationResult?.EnvironmentPerformance?.Weight,
                    EnvironmentThemeAssessment = rpcCompanyESGComputationResult?.EnvironmentPerformance?.Assessment.Name,
                    SocialThemeScore = rpcCompanyESGComputationResult?.SocialPerformance?.Score,
                    SocialThemeWeight = rpcCompanyESGComputationResult?.SocialPerformance?.Weight,
                    SocialThemeAssessment = rpcCompanyESGComputationResult?.SocialPerformance?.Assessment.Name,
                    GovernanceThemeScore = rpcCompanyESGComputationResult?.GovernancePerformance?.Score,
                    GovernanceThemeWeight = rpcCompanyESGComputationResult?.GovernancePerformance?.Weight,
                    GovernanceThemeAssessment = rpcCompanyESGComputationResult?.GovernancePerformance?.Assessment.Name,
                    PreparednessScore = rpcCompanyESGComputationResult?.PreparednessScore?.Score,
                    DisclosureScore = rpcCompanyESGComputationResult?.DisclosureScore?.Score,
                    QuantitativePerformanceScore = rpcCompanyESGComputationResult?.QuantitativePerformanceScore?.Score,
                    QualitativePerformanceControversiesScore = rpcCompanyESGComputationResult?.QualitativePerformanceScore?.Score,
                    SplCode = cd.SPLCode,
                    AnnualReport = cd.AnnualReport,
                    CsrReportDate = cd.CSRReportDate,
                    LatestFeedbackRequestDate = cd.LatestFeedbackRequest,
                    LastFullReview = cd.LastUpdate,
                };
            }
                )
            .Select(cd =>
            {
                cd.CutoffValues = !string.IsNullOrEmpty(cd.CompanyAssessment)
                                && esgFocusThresholdsCollectionDic.ContainsKey(cd.PeerGroup)
                                && esgFocusThresholdsCollectionDic[cd.PeerGroup].ContainsKey(cd.CompanyAssessment)
                                ? esgFocusThresholdsCollectionDic[cd.PeerGroup][cd.CompanyAssessment] : string.Empty;

                cd.EnvironmentAssessmentCutoffValues = !string.IsNullOrEmpty(cd.CompanyAssessment)
                                && esgFocusThresholdsCollectionDic.ContainsKey(cd.PeerGroup)
                                && esgFocusThresholdsCollectionDic[cd.PeerGroup].ContainsKey(cd.CompanyAssessment)
                                ? esgFocusThresholdsCollectionDic[cd.PeerGroup][cd.CompanyAssessment] : string.Empty;

                cd.GovernanceAssessmentCutoffValues = !string.IsNullOrEmpty(cd.CompanyAssessment)
                                && esgFocusThresholdsCollectionDic.ContainsKey(cd.PeerGroup)
                                && esgFocusThresholdsCollectionDic[cd.PeerGroup].ContainsKey(cd.CompanyAssessment)
                                ? esgFocusThresholdsCollectionDic[cd.PeerGroup][cd.CompanyAssessment] : string.Empty;

                cd.SocialAssessmentCutoffValues = !string.IsNullOrEmpty(cd.CompanyAssessment)
                                && esgFocusThresholdsCollectionDic.ContainsKey(cd.PeerGroup)
                                && esgFocusThresholdsCollectionDic[cd.PeerGroup].ContainsKey(cd.CompanyAssessment)
                                ? esgFocusThresholdsCollectionDic[cd.PeerGroup][cd.CompanyAssessment] : string.Empty;
                return cd;
            });

            return companyLevelData;
        }

        private IEnumerable<CompanyKeyEsgIssueSheetDto> GetCompanyKeyESGIssuesData(IEnumerable<CorporateDataDto> corporateData)
        {
            var data = corporateData.Select(cd =>
            {
                var keyESGIssue1 = cd.CompanyKeyESGIssues.FirstOrDefault()?.KeyESGIssues.OrderBy(x => x.SortKey).FirstOrDefault();
                var keyESGIssue2 = cd.CompanyKeyESGIssues.FirstOrDefault()?.KeyESGIssues.OrderBy(x => x.SortKey).Skip(1).Take(1).FirstOrDefault();
                var keyESGIssue3 = cd.CompanyKeyESGIssues.FirstOrDefault()?.KeyESGIssues.OrderBy(x => x.SortKey).Skip(2).Take(1).FirstOrDefault();

                return new CompanyKeyEsgIssueSheetDto
                {
                    CapitalIqId = cd.CapitalIqId,
                    SubPeergroupCode = cd.SustainalyticsSubIndustryCode,
                    SubPeergroupName = cd.SustainalyticsSubIndustry,

                    MainKeyESGExternalId1 = keyESGIssue1?.ExternalId,
                    MainKeyESGIssueName1 = keyESGIssue1?.Name,
                    SustainabilityImpact1 = keyESGIssue1?.SustainabilityImpact,
                    BusinessImpact1 = keyESGIssue1?.BussinessImpact,
                    Comment1 = keyESGIssue1?.Comment,

                    MainKeyESGExternalId2 = keyESGIssue2?.ExternalId,
                    MainKeyESGIssueName2 = keyESGIssue2?.Name,
                    SustainabilityImpact2 = keyESGIssue2?.SustainabilityImpact,
                    BusinessImpact2 = keyESGIssue2?.BussinessImpact,
                    Comment2 = keyESGIssue2?.Comment,

                    MainKeyESGExternalId3 = keyESGIssue3?.ExternalId,
                    MainKeyESGIssueName3 = keyESGIssue3?.Name,
                    SustainabilityImpact3 = keyESGIssue3?.SustainabilityImpact,
                    BusinessImpact3 = keyESGIssue3?.BussinessImpact,
                    Comment3 = keyESGIssue3?.Comment,
                };
            });

            return data;
        }

        private IEnumerable<KeyEsgIssueIndicatorSheetDto> GetKeyESGIssuesIndicatorData(IEnumerable<CorporateDataDto> corporateData)
        {
            var keyESGIssueIndicatorsData = corporateData.SelectMany(cd => cd.CompanyKeyESGIssues
                                                                            .SelectMany(cesgi => cesgi.KeyESGIssues
                                                                            .SelectMany(kei => kei.KeyEsgIssueIndicators
                                                                            .Select(keii => new KeyEsgIssueIndicatorSheetDto
                                                                            {
                                                                                CapitalIqId = cd.CapitalIqId,
                                                                                CompanyType = cd.CompanyType,
                                                                                PeerGroupName = cd.PeerGroup,
                                                                                PeerGroupCode = cd.SustainalyticsSubIndustryCode,
                                                                                MainKeyESGIssueSelected = kei.Name,
                                                                                EventIndicatorCode = keii.Number,
                                                                                IndicatorName = keii.Name,
                                                                                Research = keii.HiddenToClient
                                                                            }
                                                                            ))));

            return keyESGIssueIndicatorsData;
        }

        private IEnumerable<IndicatorLevelSheetDto> GetIndicatorLevelData(IEnumerable<CorporateDataDto> corporateData)
        {
            var indicatorLevelData = corporateData.SelectMany(cd => cd.RPIndicatorExtendedResults.Select(
                                                                                rpie => new IndicatorLevelSheetDto
                                                                                {
                                                                                    CapitalIqId = cd.CapitalIqId,
                                                                                    CompanyType = cd.CompanyType,
                                                                                    PeerGroupName = cd.PeerGroup,
                                                                                    PeerGroupCode = cd.SustainalyticsSubIndustryCode,
                                                                                    SubPeerGroupCode = cd.SubPeerGroupId,
                                                                                    SubPeerGroupName = cd.SubPeerGroup,
                                                                                    IndicatorNumber = rpie.Number,
                                                                                    IndicatorName = rpie.Template?.Name,
                                                                                    Theme = rpie.Template?.ThemeName,
                                                                                    Category = rpie.Template?.IndicatorCategory,
                                                                                    IndicatorRawScore = rpie.SelectedAnswer?.Score,
                                                                                    IndicatorAnswerCategory = rpie.SelectedAnswer?.Text,
                                                                                    Comments = rpie.Comments ?? string.Empty,
                                                                                    Weight = rpie.Weight,
                                                                                    WeightedScore = rpie.WeightedScore,
                                                                                    DataTable = rpie.DataTable != null && rpie.DataTable.Any()

                                                                                }));

            return indicatorLevelData;
        }

        private void AddIndustryLeadHeaders(IWorksheet worksheet)
        {
            IRange cells = worksheet.Cells;


            cells[0, (int)IndustryLeadRowsEnum.CapitalIqId].Value = "Capital Iq Id";
            cells[0, (int)IndustryLeadRowsEnum.PeerGroup].Value = "Peer Group";
            cells[0, (int)IndustryLeadRowsEnum.IndustryLead].Value = "Industry Lead";
        }

        private void AddSourcesHeaders(IWorksheet worksheet)
        {
            IRange cells = worksheet.Cells;


            cells[0, (int)SourcesRowsEnum.CapitalIqId].Value = "Capital Iq Id";
            cells[0, (int)SourcesRowsEnum.IndicatorCode].Value = "Indicator Code";
            cells[0, (int)SourcesRowsEnum.IndicatorName].Value = "Indicator Name";
            cells[0, (int)SourcesRowsEnum.Sources].Value = "Sources";
        }

        private void AddIndicatorLevelHeaders(IWorksheet worksheet)
        {
            IRange cells = worksheet.Cells;

            cells[0, (int)IndicatorLevelRowsEnum.CapitalIqId].Value = "Capital Iq Id";
            cells[0, (int)IndicatorLevelRowsEnum.CompanyType].Value = "Company Type";
            cells[0, (int)IndicatorLevelRowsEnum.PeerGroupCode].Value = "Peer Group Code";
            cells[0, (int)IndicatorLevelRowsEnum.PeerGroupName].Value = "Peer Group Name";
            cells[0, (int)IndicatorLevelRowsEnum.SubPeerGroupCode].Value = "SubPeer Group Code";
            cells[0, (int)IndicatorLevelRowsEnum.SubPeerGroupName].Value = "SubPeer Group Name";

            cells[0, (int)IndicatorLevelRowsEnum.IndicatorNumber].Value = "Indicator Number";
            cells[0, (int)IndicatorLevelRowsEnum.IndicatorName].Value = "Indicator Name";
            cells[0, (int)IndicatorLevelRowsEnum.Theme].Value = "Theme";
            cells[0, (int)IndicatorLevelRowsEnum.Category].Value = "Category";
            cells[0, (int)IndicatorLevelRowsEnum.IndicatorRawScore].Value = "Indicator Raw Score";
            cells[0, (int)IndicatorLevelRowsEnum.IndicatorAnswerCategory].Value = "Indicator Answer Category";
            cells[0, (int)IndicatorLevelRowsEnum.IndicatorAnalystCommentary].Value = "Indicator Analyst Commentary";
            cells[0, (int)IndicatorLevelRowsEnum.IndicatorWeight].Value = "Indicator Weight";
            cells[0, (int)IndicatorLevelRowsEnum.IndicatorWeightScore].Value = "Indicator Weight Score";
            cells[0, (int)IndicatorLevelRowsEnum.IndicatorDataTable].Value = "Indicator Data Table";
        }

        private void AddCompanyLevelHeaders(IWorksheet worksheet)
        {
            IRange cells = worksheet.Cells;

            cells[0, (int)CompanyLevelRowsEnum.CapitalIqId].Value = "Capital Iq Id";
            cells[0, (int)CompanyLevelRowsEnum.AnalystCommentary].Value = "Analyst Commentary";
            cells[0, (int)CompanyLevelRowsEnum.EscTotalScoreOverallScore].Value = "Esc Total Score Overall Score";
            cells[0, (int)CompanyLevelRowsEnum.CompanyAssessment].Value = "Company Assessment";
            cells[0, (int)CompanyLevelRowsEnum.CutoffValues].Value = "Cutoff Values";
            cells[0, (int)CompanyLevelRowsEnum.EnvironmentThemeScore].Value = "Environment Theme Score";
            cells[0, (int)CompanyLevelRowsEnum.EnvironmentThemeWeight].Value = "Environment Theme Weight";
            cells[0, (int)CompanyLevelRowsEnum.EnvironmentThemeAssessment].Value = "Environment Theme Assessment";
            cells[0, (int)CompanyLevelRowsEnum.EnvironmentAssessmentCutoffValues].Value = "Environment Assessment Cutoff Values";
            cells[0, (int)CompanyLevelRowsEnum.SocialThemeScore].Value = "Social Theme Score";
            cells[0, (int)CompanyLevelRowsEnum.SocialThemeWeight].Value = "Social Theme Weight";
            cells[0, (int)CompanyLevelRowsEnum.SocialThemeAssessment].Value = "Social Theme Assessment";
            cells[0, (int)CompanyLevelRowsEnum.SocialAssessmentCutoffValues].Value = "Social Assessment Cutoff Values";
            cells[0, (int)CompanyLevelRowsEnum.GovernanceThemeScore].Value = "Governance Theme Score";
            cells[0, (int)CompanyLevelRowsEnum.GovernanceThemeWeight].Value = "Governance Theme Weight";
            cells[0, (int)CompanyLevelRowsEnum.GovernanceThemeAssessment].Value = "Governance Theme Assessment";
            cells[0, (int)CompanyLevelRowsEnum.GovernanceAssessmentCutoffValues].Value = "Governance Assessment Cutoff Values";
            cells[0, (int)CompanyLevelRowsEnum.PreparednessScore].Value = "Preparedness Score";
            cells[0, (int)CompanyLevelRowsEnum.DisclosureScore].Value = "Disclosure Score";
            cells[0, (int)CompanyLevelRowsEnum.QuantitativePerformanceScore].Value = "Quantitative Performance Score";
            cells[0, (int)CompanyLevelRowsEnum.QualitativePerformanceControversiesScore].Value = "Qualitative Performance Controversies Score";
            cells[0, (int)CompanyLevelRowsEnum.SplCode].Value = "SPL Code";
            cells[0, (int)CompanyLevelRowsEnum.AnnualReport].Value = "Annual Report Date";
            cells[0, (int)CompanyLevelRowsEnum.CsrReportDate].Value = "CSR Report Date";
            cells[0, (int)CompanyLevelRowsEnum.LatestFeedbackRequestDate].Value = "Latest Feedback Request Date";
            cells[0, (int)CompanyLevelRowsEnum.LastFullReview].Value = "Last Full Review";
        }

        private void AddCompanyKeyESGIssuesHeaders(IWorksheet worksheet)
        {
            IRange cells = worksheet.Cells;

            cells[0, (int)CompanyKeyEsgIssueRowsEnum.CapitalIqId].Value = "Capital Iq Id";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.SubPeergroupCode].Value = "Peergroup Code";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.SubPeergroupName].Value = "Peergrou pName";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGExternalId1].Value = "Main Key ESG ExternalId1";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGIssueName1].Value = "Main Key ESG IssueName1";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.SustainabilityImpact1].Value = "Sustainability Impact1";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.BusinessImpact1].Value = "Business Impact1";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.Comment1].Value = "Comment1";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGExternalId2].Value = "Main Key ESG ExternalId2";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGIssueName2].Value = "Main Key ESG IssueName2";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.SustainabilityImpact2].Value = "Sustainability Impact2";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.BusinessImpact2].Value = "Business Impact2";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.Comment2].Value = "Comment2";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGExternalId3].Value = "Main Key ESG ExternalId3";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGIssueName3].Value = "Main Key ESG Issue Name3";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.SustainabilityImpact3].Value = "Sustainability Impact3";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.BusinessImpact3].Value = "Business Impact3";
            cells[0, (int)CompanyKeyEsgIssueRowsEnum.Comment3].Value = "Comment3";
        }

        private void AddKeyESGIssueIndicatorsHeaders(IWorksheet worksheet)
        {
            IRange cells = worksheet.Cells;

            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.CapitalIqId].Value = "Capital Iq Id";
            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.CompanyType].Value = "Company Type";
            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.PeerGroupName].Value = "PeerGroup Name";
            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.PeerGroupCode].Value = "PeerGroup Code";
            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.MainKeyESGIssueSelected].Value = "Main Key ESG Issue Selected";
            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.EventIndicatorCode].Value = "Event Indicator Code";
            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.IndicatorName].Value = "Indicator Name";
            cells[0, (int)KeyEsgIssueIndicatorRowsEnum.Research].Value = "Research";
        }

        private void AddAllKeyEsgIssuesHeaders(IWorksheet worksheet)
        {
            IRange cells = worksheet.Cells;

            cells[0, (int)KeyEsgIssueRowsEnum.ExternalId].Value = "External Id";
            cells[0, (int)KeyEsgIssueRowsEnum.KeyESGIssueName].Value = "Key ESG Issue Name";
            cells[0, (int)KeyEsgIssueRowsEnum.KeyESGIssueDescription].Value = "Key ESG Issue Description";
        }

        private void AddSourcesRows(IWorksheet worksheet, IEnumerable<SourceSheetDto> data)
        {
            IRange cells = worksheet.Cells;

            int row = 1;
            foreach (var item in data)
            {
                cells[row, (int)SourcesRowsEnum.CapitalIqId].Value = item.CapitalIqId;
                cells[row, (int)SourcesRowsEnum.IndicatorCode].Value = item.IndicatorCode;
                cells[row, (int)SourcesRowsEnum.IndicatorName].Value = item.IndicatorName;
                cells[row, (int)SourcesRowsEnum.Sources].Value = item.Sources;

                row++;
            }
        }

        private void AddIndustryLeadRows(IWorksheet worksheet, IEnumerable<IndustryLeadSheetDto> industryLeads)
        {
            IRange cells = worksheet.Cells;

            int row = 1;
            foreach (var item in industryLeads)
            {
                cells[row, (int)IndustryLeadRowsEnum.CapitalIqId].Value = item.CapitalIqId;
                cells[row, (int)IndustryLeadRowsEnum.PeerGroup].Value = item.PeerGroup;
                cells[row, (int)IndustryLeadRowsEnum.IndustryLead].Value = item.IndustryLead;

                row++;
            }
        }
        private void AddIndicatorLevelRows(IWorksheet worksheet, IEnumerable<IndicatorLevelSheetDto> data)
        {
            IRange cells = worksheet.Cells;

            int row = 1;
            foreach (var item in data)
            {
                cells[row, (int)IndicatorLevelRowsEnum.CapitalIqId].Value = item.CapitalIqId;
                cells[row, (int)IndicatorLevelRowsEnum.CompanyType].Value = item.CompanyType;
                cells[row, (int)IndicatorLevelRowsEnum.PeerGroupCode].Value = item.PeerGroupCode;
                cells[row, (int)IndicatorLevelRowsEnum.PeerGroupName].Value = item.PeerGroupName;
                cells[row, (int)IndicatorLevelRowsEnum.SubPeerGroupCode].Value = item.SubPeerGroupCode;
                cells[row, (int)IndicatorLevelRowsEnum.SubPeerGroupName].Value = item.SubPeerGroupName;

                cells[row, (int)IndicatorLevelRowsEnum.IndicatorNumber].Value = item.IndicatorNumber;
                cells[row, (int)IndicatorLevelRowsEnum.IndicatorName].Value = item.IndicatorName;
                cells[row, (int)IndicatorLevelRowsEnum.Theme].Value = item.Theme;
                cells[row, (int)IndicatorLevelRowsEnum.Category].Value = item.Category;
                cells[row, (int)IndicatorLevelRowsEnum.IndicatorRawScore].Value = item.IndicatorRawScore;
                cells[row, (int)IndicatorLevelRowsEnum.IndicatorAnswerCategory].Value = item.IndicatorAnswerCategory;
                cells[row, (int)IndicatorLevelRowsEnum.IndicatorAnalystCommentary].Value = item.Comments;
                cells[row, (int)IndicatorLevelRowsEnum.IndicatorWeight].Value = item.Weight;
                cells[row, (int)IndicatorLevelRowsEnum.IndicatorWeightScore].Value = item.WeightedScore;
                cells[row, (int)IndicatorLevelRowsEnum.IndicatorDataTable].Value = item.DataTable ? "Yes" : "No";

                row++;
            }
        }

        private void AddCompanyLevelRows(IWorksheet worksheet, IEnumerable<CompanyLevelSheetDto> corporateData)
        {
            IRange cells = worksheet.Cells;

            int row = 1;
            foreach (var item in corporateData)
            {
                #region Company Level Sheet

                cells[row, (int)CompanyLevelRowsEnum.CapitalIqId].Value = item.CapitalIqId;
                cells[row, (int)CompanyLevelRowsEnum.AnalystCommentary].Value = item.AnalystCommentary;
                cells[row, (int)CompanyLevelRowsEnum.EscTotalScoreOverallScore].Value = item.EscTotalScoreOverallScore;
                cells[row, (int)CompanyLevelRowsEnum.CompanyAssessment].Value = item.CompanyAssessment;
                cells[row, (int)CompanyLevelRowsEnum.CutoffValues].Value = item.CutoffValues;
                cells[row, (int)CompanyLevelRowsEnum.EnvironmentThemeScore].Value = item.EnvironmentThemeScore;
                cells[row, (int)CompanyLevelRowsEnum.EnvironmentThemeWeight].Value = item.EnvironmentThemeWeight;
                cells[row, (int)CompanyLevelRowsEnum.EnvironmentThemeAssessment].Value = item.EnvironmentThemeAssessment;
                cells[row, (int)CompanyLevelRowsEnum.EnvironmentAssessmentCutoffValues].Value = item.EnvironmentAssessmentCutoffValues;
                cells[row, (int)CompanyLevelRowsEnum.SocialThemeScore].Value = item.SocialThemeScore;
                cells[row, (int)CompanyLevelRowsEnum.SocialThemeWeight].Value = item.SocialThemeWeight;
                cells[row, (int)CompanyLevelRowsEnum.SocialThemeAssessment].Value = item.SocialThemeAssessment;
                cells[row, (int)CompanyLevelRowsEnum.SocialAssessmentCutoffValues].Value = item.SocialAssessmentCutoffValues;
                cells[row, (int)CompanyLevelRowsEnum.GovernanceThemeScore].Value = item.GovernanceThemeScore;
                cells[row, (int)CompanyLevelRowsEnum.GovernanceThemeWeight].Value = item.GovernanceThemeWeight;
                cells[row, (int)CompanyLevelRowsEnum.GovernanceThemeAssessment].Value = item.GovernanceThemeAssessment;
                cells[row, (int)CompanyLevelRowsEnum.GovernanceAssessmentCutoffValues].Value = item.GovernanceAssessmentCutoffValues;
                cells[row, (int)CompanyLevelRowsEnum.PreparednessScore].Value = item.PreparednessScore;
                cells[row, (int)CompanyLevelRowsEnum.DisclosureScore].Value = item.DisclosureScore;
                cells[row, (int)CompanyLevelRowsEnum.QuantitativePerformanceScore].Value = item.QuantitativePerformanceScore;
                cells[row, (int)CompanyLevelRowsEnum.QualitativePerformanceControversiesScore].Value = item.QualitativePerformanceControversiesScore;
                cells[row, (int)CompanyLevelRowsEnum.SplCode].Value = item.SplCode;
                cells[row, (int)CompanyLevelRowsEnum.AnnualReport].Value = item.AnnualReport;
                cells[row, (int)CompanyLevelRowsEnum.CsrReportDate].Value = item.CsrReportDate;
                cells[row, (int)CompanyLevelRowsEnum.LatestFeedbackRequestDate].Value = item.LatestFeedbackRequestDate;
                cells[row, (int)CompanyLevelRowsEnum.LastFullReview].Value = item.LastFullReview;

                #endregion Company Level Sheet

                row++;
            }
        }
        private void AddCompanyKeyESGIssuesRows(IWorksheet worksheet, IEnumerable<CompanyKeyEsgIssueSheetDto> keyESGIssues)
        {
            IRange cells = worksheet.Cells;

            int row = 1;
            foreach (var item in keyESGIssues)
            {
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.CapitalIqId].Value = item.CapitalIqId;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.SubPeergroupCode].Value = item.SubPeergroupCode;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.SubPeergroupName].Value = item.SubPeergroupName;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGExternalId1].Value = item.MainKeyESGExternalId1;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGIssueName1].Value = item.MainKeyESGIssueName1;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.SustainabilityImpact1].Value = item.SustainabilityImpact1;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.BusinessImpact1].Value = item.BusinessImpact1;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.Comment1].Value = item.Comment1;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGExternalId2].Value = item.MainKeyESGExternalId2;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGIssueName2].Value = item.MainKeyESGIssueName2;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.SustainabilityImpact2].Value = item.SustainabilityImpact2;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.BusinessImpact2].Value = item.BusinessImpact2;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.Comment2].Value = item.Comment2;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGExternalId3].Value = item.MainKeyESGExternalId3;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.MainKeyESGIssueName3].Value = item.MainKeyESGIssueName3;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.SustainabilityImpact3].Value = item.SustainabilityImpact3;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.BusinessImpact3].Value = item.BusinessImpact3;
                cells[row, (int)CompanyKeyEsgIssueRowsEnum.Comment3].Value = item.Comment3;

                row++;
            }
        }

        private void AddKeyESGIssueIndicatorsRows(IWorksheet worksheet, IEnumerable<KeyEsgIssueIndicatorSheetDto> keyESGIssueIndicators)
        {
            IRange cells = worksheet.Cells;

            int row = 1;
            foreach (var item in keyESGIssueIndicators)
            {
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.CapitalIqId].Value = item.CapitalIqId;
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.CompanyType].Value = item.CompanyType;
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.PeerGroupName].Value = item.PeerGroupName;
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.PeerGroupCode].Value = item.PeerGroupCode;
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.MainKeyESGIssueSelected].Value = item.MainKeyESGIssueSelected;
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.EventIndicatorCode].Value = item.EventIndicatorCode;
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.IndicatorName].Value = item.IndicatorName;
                cells[row, (int)KeyEsgIssueIndicatorRowsEnum.Research].Value = item.Research;

                row++;
            }
        }

        private void AddAllKeyEsgIssuesRows(IWorksheet worksheet, IEnumerable<KeyEsgIssueSheetDto> keyESGIssues)
        {
            IRange cells = worksheet.Cells;

            int row = 1;
            foreach (var item in keyESGIssues)
            {

                cells[row, (int)KeyEsgIssueRowsEnum.ExternalId].Value = item.ExternalId;
                cells[row, (int)KeyEsgIssueRowsEnum.KeyESGIssueName].Value = item.Name;
                cells[row, (int)KeyEsgIssueRowsEnum.KeyESGIssueDescription].Value = item.Description;

                row++;
            }
        }
    }
}
